DROP PROCEDURE sp_cat_AddPartMaster
GO

CREATE PROCEDURE [dbo].[sp_cat_AddPartMaster]
@PKPartMaster int, @FKManufacturer int, @FKDistributor int , @FKStatus int, 
@PartNumber varchar(50), @ManufacturerPartNumber varchar(50), @Description varchar(200), 
@PurchacePrice float, @PurchaseQty float, @SalesPrice float, @SalesQty float,@Adders float, @DataSheet varchar(255), 
@Photo varchar(255),@Quote varchar(255), @UpdatedBy int
AS
BEGIN
  BEGIN TRAN A
  IF @PKPartMaster = 0 BEGIN
    INSERT INTO CT_PartMaster(FKManufacturer, FKDistributor, FKStatus, PartNumber, ManufacturerPartNumber, 
    Description, PurchacePrice, PurchaseQty, SalesPrice, SalesQty, Adders, DataSheet, Photo,Quote , UpdatedBy)
    VALUES(@FKManufacturer, @FKDistributor, @FKStatus, @PartNumber, @ManufacturerPartNumber, 
    @Description, @PurchacePrice, @PurchaseQty, @SalesPrice, @SalesQty,@Adders, @DataSheet, @Photo, @Quote, @UpdatedBy);
    IF @@ERROR<>0 BEGIN
      GOTO ErrHdl;
    END
  END  
  ELSE BEGIN
  PRINT @PKPartMaster
    UPDATE CT_PartMaster SET
      FKManufacturer = COALESCE(@FKManufacturer,FKManufacturer)
      ,FKDistributor = COALESCE(@FKDistributor,FKDistributor)
      ,FKStatus = COALESCE(@FKStatus,FKStatus)
      ,PartNumber = COALESCE(@PartNumber,PartNumber)
      ,ManufacturerPartNumber = COALESCE(@ManufacturerPartNumber,ManufacturerPartNumber)
      ,Description = COALESCE(@Description,Description)
      ,PurchacePrice = COALESCE(@PurchacePrice,PurchacePrice)
      ,PurchaseQty = COALESCE(@PurchaseQty,PurchaseQty)
      ,SalesPrice = COALESCE(@SalesPrice,SalesPrice)
      ,SalesQty = COALESCE(@SalesQty,SalesQty)
      ,Adders = COALESCE(@Adders,Adders)
      ,DataSheet = COALESCE(@DataSheet,DataSheet)
      ,Photo = COALESCE(@Photo,Photo)
      ,Quote = COALESCE(@Quote,Quote)
      ,UpdatedBy = @UpdatedBy
      ,LastUpdated = GETDATE()
    WHERE PKPartMaster = @PKPartMaster
    IF @@ERROR<>0 BEGIN
      GOTO ErrHdl;
    END
  END
  COMMIT TRAN A
  PRINT 'COMMIT!'
  GOTO EndProc
ErrHdl:
    ROLLBACK TRAN A
    PRINT 'ROLLBACK'
    RAISERROR('Cannot add/modify Part number' , 16 , 1)
EndProc:
END
GO